package com.thprojects.myphotomemories.controller;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.Bitmap.CompressFormat;
import android.graphics.BitmapFactory;
import android.util.Log;

import com.thprojects.myphotomemories.model.Album;
import com.thprojects.myphotomemories.model.Contact;
import com.thprojects.myphotomemories.model.Photo;

//SINGLETON
public class DbController extends SQLiteOpenHelper{
	
	Context mContext;
	
	//design pattern singleton	
	private static DbController singleton = null;

	private void resetDatabase(){
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS album");
		db.execSQL("DROP TABLE IF EXISTS photo");
		db.execSQL("DROP TABLE IF EXISTS contact_photo");
        onCreate(db);
	}
	
    private DbController(Context context) {
        super(context, "myPhotoMemories.db", null, 3); 
        mContext = context;
        //resetDatabase();
    }
    
    public static DbController getDbController(Context context){
    	if (singleton == null) singleton = new DbController(context);    	
    	return singleton;
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
    	
    	//album
	    db.execSQL(""+
    		"CREATE TABLE IF NOT EXISTS album ("+
    		"_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
    		"name TEXT NOT NULL, "+
    		"description TEXT "+
    		");"
		);
	    
	    //photo
	    db.execSQL(""+
    		"CREATE TABLE IF NOT EXISTS photo ("+
    		"_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
    		"path TEXT NOT NULL UNIQUE, "+
    		"name TEXT NOT NULL, "+
    		"album INTEGER NOT NULL REFERENCES album ON DELETE CASCADE ON UPDATE CASCADE, "+
    		"caption TEXT, "+
    		"location TEXT "+
    		");"
		);
	    
	    //contacts and photos
	    db.execSQL(""+
    		"CREATE TABLE IF NOT EXISTS contact_photo ("+
    		"_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
    		"lookup_key TEXT NOT NULL,"+
    		"photo INTEGER NOT NULL REFERENCES photo ON DELETE CASCADE ON UPDATE CASCADE "+
    		");"
		);
	    	    
	    Log.d("DbController", "create tabelle db");
    }
    
    public Album saveAlbum(String name, String description){
    	SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("description", description);
		int id = (int)db.insert("album", null, cv);
		db.close();
		
		Album res = new Album(name, description, id);
		
		return res;
    }
    
    public boolean isExistingAlbum(String name){
    	SQLiteDatabase db = getReadableDatabase();

    	Cursor c = db.query(false, "album", new String[]{"_id"}, "name=?", new String[]{name}, null, null, null, null);
		int res = c.getCount();
		c.close();
		db.close();
		return res!=0;
    }

    
    public List<Album> getAlbumList(){
    	List<Album> res = new LinkedList<Album>();
    	
    	SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.query(false, "album", new String[]{"_id", "name", "description"}, null, null, null, null, null, null);
		
		int idIdx = c.getColumnIndex("_id");
		int nameIdx = c.getColumnIndex("name");
		int descrIdx = c.getColumnIndex("description");
		
		while (c.moveToNext()){
			String n = c.getString(nameIdx);
			String d = c.getString(descrIdx);
			int i = c.getInt(idIdx);
			res.add(new Album(n,d,i));
		}
		
		c.close();
		db.close();
    	
    	return res;
    }
    
    public Photo savePhoto(String path, String name, String caption, String location, Album album, List<Contact> contacts){
    	SQLiteDatabase db = getWritableDatabase();
		/*inserisco il nuovo oggetto nella tabella Loghi*/
		ContentValues cv = new ContentValues();
		cv.put("path", path);
		cv.put("name", name);
		cv.put("caption", caption);
		cv.put("location", location);
		cv.put("album", album.getId());
		int id = (int)db.insert("photo", null, cv);
		
		for (Contact c: contacts){
			cv = new ContentValues();
			cv.put("lookup_key", c.getLookupKey());
			cv.put("photo", id);
			db.insert("contact_photo", null, cv);
		}
		
		db.close();
		
		Photo res = new Photo(id, path, name, caption, location, album, contacts);
		
		return res;
    }
    
    public boolean isExistingPhotoInAlbum(String path, Album a){
    	SQLiteDatabase db = getReadableDatabase();

    	Cursor c = db.query(false, "photo", new String[]{"_id"}, "path=? AND album=?", new String[]{path, ""+a.getId()}, null, null, null, null);
		int res = c.getCount();
		c.close();
		db.close();
		return res!=0;
    }
    
    public void editPhotoCaption(Photo photo, String nc){
    	SQLiteDatabase db = getWritableDatabase();
    	
    	ContentValues cv = new ContentValues();
    	cv.put("caption", nc);
    	db.update("photo", cv, "_id =?", new String[]{photo.getId()+""});
    	
    	db.close();
    }
    
    public void editPhotoLocation(Photo photo, String loc){
    	SQLiteDatabase db = getWritableDatabase();
    	
    	ContentValues cv = new ContentValues();
    	cv.put("location", loc);
    	db.update("photo", cv, "_id =?", new String[]{photo.getId()+""});
    	
    	db.close();
    }
    
    public void editPhoto(int id, String name, String caption, String location, List<Contact> contacts) {
    	SQLiteDatabase db = getWritableDatabase();
    	
    	ContentValues cv = new ContentValues();
    	cv.put("name", name);
    	cv.put("caption", caption);
    	cv.put("location", location);
    	db.update("photo", cv, "_id =?", new String[]{id+""});
    	
    	//contacts
    	
    	//delete old contacts referring to this photo
    	db.delete("contact_photo", "photo =?", new String[]{id+""});
    	//add new contacts
    	for (Contact c: contacts){
			cv = new ContentValues();
			cv.put("lookup_key", c.getLookupKey());
			cv.put("photo", id);
			db.insert("contact_photo", null, cv);
		}
    	
    	db.close();
	}
    
    public void removePhoto(Photo photo){
    	SQLiteDatabase db = getWritableDatabase();

    	db.delete("photo", "_id =?", new String[]{photo.getId()+""});

    	db.close();
    }
    
    public List<Photo> getPhotoList(Album a){
    	List<Photo> res = new LinkedList<Photo>();
    	
    	SQLiteDatabase db = getReadableDatabase();
    	
    	String albumId = a.getId()+"";
		
		Cursor c = db.query(false, "photo", new String[]{"_id", "path", "name", "caption", "location"}, "album=?", new String[]{albumId}, null, null, null, null);
		
		int idIdx = c.getColumnIndex("_id");
		int pathIdx = c.getColumnIndex("path");
		int nameIdx = c.getColumnIndex("name");
		int captionIdx = c.getColumnIndex("caption");
		int locationIdx = c.getColumnIndex("location");
		
		while (c.moveToNext()){
			int i = c.getInt(idIdx);
			String p = c.getString(pathIdx);
			String na = c.getString(nameIdx);			
			String cap = c.getString(captionIdx);
			String l = c.getString(locationIdx);
			
			res.add(new Photo(i, p, na, cap, l, a, getContactsFromPhoto(i)));
		}
		
		c.close();
		db.close();
    	
    	return res;
    }
	
    public Photo getAPhotoFromAlbum(Album a){
    	SQLiteDatabase db = getReadableDatabase();
    	
    	String albumId = a.getId()+"";
		
		Cursor c = db.query(false, "photo", new String[]{"_id", "path", "name", "caption", "location"}, "album=?", new String[]{albumId}, null, null, null, null);
		
		int idIdx = c.getColumnIndex("_id");
		int pathIdx = c.getColumnIndex("path");
		int nameIdx = c.getColumnIndex("name");
		int captionIdx = c.getColumnIndex("caption");
		int locationIdx = c.getColumnIndex("location");
		
		while (c.moveToNext()){
			int i = c.getInt(idIdx);
			String p = c.getString(pathIdx);
			String na = c.getString(nameIdx);			
			String cap = c.getString(captionIdx);
			String l = c.getString(locationIdx);
			
			c.close();
			db.close();
			return new Photo(i, p, na, cap, l, a, getContactsFromPhoto(i));
		}
		
		c.close();
		db.close();
    	
    	return null;
    }
    
    public Photo getPhotoFromId(int id){
    	SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.query(false, "photo", new String[]{"_id", "path", "name", "caption", "location", "album"}, "_id=?", new String[]{id+""}, null, null, null, null);
		
		int idIdx = c.getColumnIndex("_id");
		int pathIdx = c.getColumnIndex("path");
		int nameIdx = c.getColumnIndex("name");
		int captionIdx = c.getColumnIndex("caption");
		int locationIdx = c.getColumnIndex("location");
		int albumIdx = c.getColumnIndex("album");
		
		while (c.moveToNext()){
			int i = c.getInt(idIdx);
			String p = c.getString(pathIdx);
			String na = c.getString(nameIdx);			
			String cap = c.getString(captionIdx);
			String l = c.getString(locationIdx);
			int a = c.getInt(albumIdx);
			
			c.close();
			db.close();
			return new Photo(i, p, na, cap, l, getAlbumFromId(a), getContactsFromPhoto(i));
		}
		
		c.close();
		db.close();
    	
    	return null;
    }
    
    public Album getAlbumFromId(int id){
    	SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.query(false, "album", new String[]{"_id", "name", "description"}, "_id=?", new String[]{id+""}, null, null, null, null);
		
		int descIdx = c.getColumnIndex("description");
		int nameIdx = c.getColumnIndex("name");
		
		while (c.moveToNext()){
			String d = c.getString(descIdx);
			String na = c.getString(nameIdx);			
			
			c.close();
			db.close();
			return new Album(na, d, id);
		}
		
		c.close();
		db.close();
    	
    	return null;
    }
   
    
    private List<Contact> getContactsFromPhoto(int phId){
    	
    	SQLiteDatabase db = getReadableDatabase();
    	
    	String id = phId+"";
		
		Cursor c = db.query(false, "contact_photo", new String[]{"lookup_key"}, "photo=?", new String[]{id}, null, null, null, null);
		
		List<Contact> res = new ArrayList<Contact>();
		
		int lkIdx = c.getColumnIndex("lookup_key");
		
		while (c.moveToNext()){
			String lk = c.getString(lkIdx);
			String name = ContactsController.getDisplayName(mContext, lk);
			res.add(new Contact(name, lk));
		}
		
		c.close();
		db.close();
    	
    	return res;
    }
    
	

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS album");
		db.execSQL("DROP TABLE IF EXISTS photo");
		db.execSQL("DROP TABLE IF EXISTS contact_photo");
        onCreate(db);
	}

	
}

